#include "../../include/sqlhelper.hpp"
#include "../../include/DBhandler.hpp"
#include <iostream>

// #define DB_PATHNAME "photography_forum.db"
#define DB_PATHNAME "../../source/database/photography_forum.db"

void createDatabase() {
    xzt::SqlHelper sql_helper(DB_PATHNAME);
    
    bool ret = sql_helper.open();
    if(!ret)
    {
        return;
    }

    std::string sql_users = "CREATE TABLE IF NOT EXISTS users("
                            "user_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                            "username VARCHAR(50) NOT NULL UNIQUE, "
                            "password VARCHAR(255) NOT NULL, "
                            "email VARCHAR(100) NOT NULL UNIQUE, "
                            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP);";

    std::string sql_posts = "CREATE TABLE IF NOT EXISTS posts("
                            "post_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                            "user_id INTEGER NOT NULL, "
                            "title VARCHAR(255) NOT NULL, "
                            "content TEXT NOT NULL, "
                            "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, "
                            "updated_at DATETIME, "
                            "FOREIGN KEY(user_id) REFERENCES users(user_id));";

    std::string sql_photos = "CREATE TABLE IF NOT EXISTS photos("
                             "photo_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                             "user_id INTEGER NOT NULL, "
                             "title VARCHAR(255), "
                             "category VARCHAR(100), "
                             "image_path VARCHAR(255) NOT NULL, "
                             "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, "
                             "FOREIGN KEY(user_id) REFERENCES users(user_id));";

    std::string sql_comments = "CREATE TABLE IF NOT EXISTS comments("
                               "comment_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                               "user_id INTEGER NOT NULL, "
                               "post_id INTEGER, "
                               "photo_id INTEGER, "
                               "content TEXT NOT NULL, "
                               "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, "
                               "FOREIGN KEY(user_id) REFERENCES users(user_id), "
                               "FOREIGN KEY(post_id) REFERENCES posts(post_id), "
                               "FOREIGN KEY(photo_id) REFERENCES photos(photo_id));";

    std::string sql_events = "CREATE TABLE IF NOT EXISTS events("
                             "event_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                             "user_id INTEGER NOT NULL, "
                             "title VARCHAR(255) NOT NULL, "
                             "description TEXT, "
                             "location VARCHAR(255), "
                             "event_date DATETIME NOT NULL, "
                             "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, "
                             "FOREIGN KEY(user_id) REFERENCES users(user_id));";

    sql_helper.exec(sql_users, nullptr, nullptr);
    sql_helper.exec(sql_posts, nullptr, nullptr);
    sql_helper.exec(sql_photos, nullptr, nullptr);
    sql_helper.exec(sql_comments, nullptr, nullptr);
    sql_helper.exec(sql_events, nullptr, nullptr);

    sql_helper.close();
}


void insertDataIntoPhotosTable()
{
    // 打开数据库
    xzt::SqlHelper sql_helper(DB_PATHNAME);
    bool ret = sql_helper.open();
    if(!ret)
    {
        return;
    }
    // 获取数据位置
    std::string filename = "mountion.jpg";
    std::string file_path = "../../source/photos/" + filename;

    // 插入数据
    // --------- <方式1> ---------  // 推荐方式，注意columns和values的顺序一致
    std::vector<std::string> columns = {"user_id", "title", "category", "image_path"};
    std::vector<std::string> values = {"1", "n_title", "n_category", file_path};
    sql_helper.insert_data("photos", columns, values);

    // 关闭数据库
    sql_helper.close();

    // --------- <方式2> ---------
    // int user_id = 1; // 假设用户id为1
    // std::string title = "m_title";
    // std::string description = "m_description";
    // std::string category = "m_category";

    // std::string sql = "INSERT INTO photos (user_id, title, category, image_path) "
    //                       "VALUES (?, ?, ?, ?)";
    // sqlite3_stmt *stmt;
    // sqlite3_prepare_v2(sql_helper.get_handler(), sql.c_str(), -1, &stmt, nullptr);
    // sqlite3_bind_int(stmt, 1, user_id);
    // sqlite3_bind_text(stmt, 2, title.c_str(), -1, SQLITE_STATIC);
    // sqlite3_bind_text(stmt, 3, category.c_str(), -1, SQLITE_STATIC);
    // sqlite3_bind_text(stmt, 4, file_path.c_str(), -1, SQLITE_STATIC);

    // sqlite3_step(stmt);
    // sqlite3_finalize(stmt);
    // sqlite3_close(sql_helper.get_handler());

    // 刷新图片总数量
    ++xzt::PhotoDBHandler::photo_num;
}
int main() 
{
    createDatabase();
    std::cout << "Database and tables created successfully!" << std::endl;
    insertDataIntoPhotosTable();
    std::cout << "Data inserted successfully!" << std::endl;
    return 0;
}
